package com.returnsoft.resource.eao;

import java.util.ArrayList;
import java.util.List;

import javax.ejb.LocalBean;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.returnsoft.resource.dto.TrainerDto;
import com.returnsoft.resource.entity.Trainer;
import com.returnsoft.resource.exception.EaoExcepcion;

@LocalBean
@Stateless
public class TrainerEao {

	@PersistenceContext
	private EntityManager em;

	public Trainer add(Trainer trainer) throws EaoExcepcion {
		try {

			trainer = em.merge(trainer);

			em.flush();

			return trainer;
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}
	}

	public Trainer findByUserId(Integer userId) throws EaoExcepcion {

		try {
			Query q = em.createQuery("SELECT a.id " + "FROM Trainer a "
					+ "WHERE a.userId = :userId");

			q.setParameter("userId", userId);

			Integer id = (Integer) q.getSingleResult();

			Trainer trainer = new Trainer();
			trainer.setId(id);

			return trainer;

		} catch (NoResultException e) {
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}

	}

	public Trainer findById(Integer trainerId) throws EaoExcepcion {

		try {

			Trainer trainer = em.find(Trainer.class, trainerId);

			return trainer;

		} catch (NoResultException e) {
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}

	}

	@SuppressWarnings("unchecked")
	public List<TrainerDto> findList(List<Integer> areasId, Boolean areaNull,
			String name, String documentNumber) throws EaoExcepcion {
		try {
			String query = "SELECT "
					+ "r.id "
					+ ",p.firstname,p.lastname,p.document_number,group_concat(a.name separator ' - ') as areas "
					+ "FROM recruitment.trainer r "
					+ "left join security.user u on u.id=r.user_id "
					+ "left join security.person p on p.id=u.person_id "
					+ "left join recruitment.trainer_area ra on ra.trainer_id=r.id "
					+ "left join recruitment.area a on a.id=ra.area_id "
					+ "where r.id>0 ";

			if (areasId != null && areasId.size() > 0) {
				query += " and ra.area_id in ";
				query += " ( ";
				for (int i = 0; i < areasId.size(); i++) {
					Integer campaignId = areasId.get(i);
					if (i < areasId.size() - 1) {
						query += campaignId + ",";
					} else {
						query += campaignId;
					}
				}
				query += " ) ";
			} else {
				if (areaNull) {
					query += " and ra.area_id is null";
				}
			}

			if (documentNumber != null && !documentNumber.equals("")) {
				query += " and p.document_number like '%" + documentNumber
						+ "%'";
			}

			if (name != null && !name.equals("")) {
				query += " and (p.firstname like '%" + name
						+ "%' or p.lastname like '%" + name + "%')";
			}

			query += " group by r.id";

			Query q = em.createNativeQuery(query);

			List<Object[]> objectsList = (List<Object[]>) q.getResultList();

			List<TrainerDto> entities = new ArrayList<TrainerDto>();
			if (objectsList.size() > 0) {
				for (Object[] object : objectsList) {

					Integer trainerId = (Integer) object[0];
					String trainerFirstname = (String) object[1];
					String trainerLastname = (String) object[2];
					String trainerDocumentNumber = (String) object[3];
					String trainerAreas = (String) object[4];

					TrainerDto entity = new TrainerDto();
					entity.setId(trainerId);
					entity.setFirstname(trainerFirstname);
					entity.setLastname(trainerLastname);
					entity.setDocumentNumber(trainerDocumentNumber);
					entity.setAreasList(trainerAreas);
					entities.add(entity);

				}
			}

			return entities;
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}
	}

	@SuppressWarnings("unchecked")
	public List<TrainerDto> findAll() throws EaoExcepcion {
		try {
			String query = "SELECT " + "r.id "
					+ ",p.firstname,p.lastname,p.document_number "
					+ "FROM recruitment.trainer r "
					+ "left join security.user u on u.id=r.user_id "
					+ "left join security.person p on p.id=u.person_id "
					+ "group by r.id";

			Query q = em.createNativeQuery(query);

			List<Object[]> objectsList = (List<Object[]>) q.getResultList();

			List<TrainerDto> entities = new ArrayList<TrainerDto>();
			if (objectsList.size() > 0) {
				for (Object[] object : objectsList) {

					Integer trainerId = (Integer) object[0];
					String trainerFirstname = (String) object[1];
					String trainerLastname = (String) object[2];
					String trainerDocumentNumber = (String) object[3];

					TrainerDto entity = new TrainerDto();
					entity.setId(trainerId);
					entity.setFirstname(trainerFirstname);
					entity.setLastname(trainerLastname);
					entity.setDocumentNumber(trainerDocumentNumber);
					entities.add(entity);

				}
			}

			return entities;
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}
	}

}
